package com.bbva.gptf.back.dominio;

import java.util.List;

import org.springframework.roo.addon.dbre.RooDbManaged;
import org.springframework.roo.addon.entity.RooEntity;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.tostring.RooToString;


@RooJavaBean
@RooToString
@RooEntity(identifierType = Tgpt025InstrumentoPK.class, versionField = "", table = "TGPT025_INSTRUMENTO", schema = "DBSGPT")
@RooDbManaged(automaticallyDelete = true)
public class Tgpt025Instrumento {

	public static List<Tgpt025Instrumento> obtieneInstrumentoPorTValorEmisoraYSerie(String tipoMercado, String tipoValor, String emisora, String serie, int plazoMin, int plazoMax) {
		String qlString = "FROM Tgpt025Instrumento INST WHERE INST.tpMercado = :tipoMercado"
				+ " AND INST.id.cdValorBmv = :tipoValor";

		if (!emisora.equals("") && emisora != null)
			qlString = qlString + " AND TRIM( inst.id.cdEmisora) = '" + emisora + "'";
		
		if (!serie.equals("") && serie != null) {
			qlString = qlString + " AND INST.id.cdSerie = '" + serie + "'";
		}
		
		if(plazoMax > 0){
			qlString = qlString + " AND INST.duPlazo BETWEEN " + plazoMin +" AND "+ plazoMax;
		}
		return entityManager().createQuery(qlString, Tgpt025Instrumento.class)
				.setParameter("tipoMercado", tipoMercado)
				.setParameter("tipoValor", tipoValor)
				.getResultList();

	}
	
	public static List<Tgpt025Instrumento> obtieneInstrumentos(String tipoMercado, String cdValorBmv,
			String cdEmisora, String cdSerie, Long plazoMin, Long plazoMax) {
		
		String query =   "select inst from Tgpt025Instrumento inst, Vgpt005ClasTvMer ct"
					   + " where inst.id.cdValorBmv = ct.id.cdValorBmv"
					   + " and ct.id.tpMercado = :tipoMercado";
		if (cdValorBmv != null && !cdValorBmv.equals("")) {
			cdValorBmv = cdValorBmv + "%";
			query = query + " and inst.id.cdValorBmv like '" + cdValorBmv + "'";
		}
		if (cdEmisora != null && !cdEmisora.equals("")) {
			cdEmisora = cdEmisora + "%";
			query = query + " and inst.id.cdEmisora like '" + cdEmisora + "'";
		}
		if (cdSerie != null && !cdSerie.equals("")) {
			query = query + " and inst.id.cdSerie = '" + cdSerie + "'";
		}
		if (plazoMax > 0L) {
			query = query + " and NVL(inst.ctDiaPorVencer, 0) >= " + plazoMin;
			query = query + " and NVL(inst.ctDiaPorVencer, 0) < " + plazoMax;
		}
		query = query + " ORDER BY inst.id.cdValorBmv, inst.id.cdEmisora, inst.id.cdSerie";
		return entityManager().createQuery(query, Tgpt025Instrumento.class)
							  .setParameter("tipoMercado", tipoMercado)
							  .getResultList();
			
	}

}

